Creating a Development Dump from Production

This guide explains how to create a development database dump from production data. The process is automated through a single command that handles the entire workflow.

Overview

The entire process is handled by a single command that:

1. Creates dumps from production
2. Imports the dumps locally
3. Prepares the data for development
4. Creates a final development-ready dump

Prerequisites

1. Zoo running locally

2. Production database read-only credentials in .../farfalla/mysqldump-farfalla_readonly.cnf:

   [mysqldump]
   host=production-host
   user=readonly-user
   password=readonly-password
   port=3306

3. .../farfalla/database/schema/20241111_farfalla_dev_local_gateways.gz file for local gateways data

Running the Process

Simply run:

php artisan pla:refresh-local-db-from-prod

This command performs the following steps automatically:

1. Creates a dated directory in storage/app/db/YYYYMMDD/
2. Dumps and imports:
   • Database schema
   • SingleStore projections
   • Minimal data from essential tables
3. Automatically triggers pla:prepare-local-db-for-dev which:
   • Imports local gateways data
   • Updates tenant settings
   • Configures development features
   • Sets up test data
4. Creates a final development-ready dump

Output Files

The command creates these files in the dated directory:

• YYYYMMDD___farfalla___prod_dump___schema.sql.gz - Database schema
• YYYYMMDD___farfalla___prod_dump___schema_projections.sql.gz - SingleStore projections
• YYYYMMDD___farfalla___prod_dump___minimal_data.sql.gz - Minimal data from essential tables
• YYYYMMDD___farfalla___minimal___ready_for_dev.sql.gz - Final development-ready dump
• ran_at_{timestamp} - Empty file to track when the dump was created

Final Output

After the command completes successfully, you'll have a development-ready database dump at:

.../farfalla/storage/app/db/YYYYMMDD/YYYYMMDD___farfalla___minimal___ready_for_dev.sql.gz

This dump can be shared with the team and used to set up local development environments.

Backup and History

• All previous dumps in a date directory are automatically backed up to .../farfalla/storage/app/db/YYYYMMDD/backup/{timestamp}/ before new dumps are created
• Each dump is tracked with a timestamp file
• You can specify different dates to maintain multiple versions

What the Preparation Step Does

When pla:refresh-local-db-from-prod triggers pla:prepare-local-db-for-dev, it performs these adjustments:

1. Database Safety

   • Checks if database was already prepared to avoid corruption
   • Only runs in local environment
   • Clears Redis databases

2. Domain Configuration

   • Sets main tenant domain to farfalla.localhost
   • Updates all other tenants to use {slug}.farfalla.localhost
   • Prefixes all tenant names with "lcl -"
   • Clears landing page redirects

3. User Management

   • Creates super admin users for all tenants
   • Sets default password to 'secret'
   • Uses local@publica.la as the admin email

4. Data Cleanup

   • Removes orphaned sign-up intents
   • Cleans tracking and analytics data
   • Removes custom styles and policies
   • Optionally removes user plans without matching orders

5. Security

   • Marks the database as prepared to prevent multiple runs
   • Disables activity logging during the process

This preparation ensures the database is ready for local development while maintaining data integrity and security.

Troubleshooting

1. If you get connection errors, verify:

   • The mysqldump-farfalla_readonly.cnf file exists and has correct credentials
   • You can connect to the production database from your machine
   • Your local SingleStore instance is running

2. If the dump is missing data:

   • Check if all required tables exist in production
   • Verify you have proper permissions on the production database
   • Check the logs for any skipped tables or errors

3. If preparation fails:

   • Ensure you have the latest gateway data file
   • Check if all required tables were properly dumped
   • Verify your local database permissions

---